const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');
const { app } = require('electron');
const DatabaseMigration = require('./migration');

class DatabaseManager {
  constructor() {
    this.db = null;
    this.isInitialized = false;
    this.migration = null;
    this.init();
  }

  init() {
    try {
      // 获取用户数据目录
      const userDataPath = app.getPath('userData');
      const dbPath = path.join(userDataPath, 'achievements.db');
      
      console.log('数据库文件路径:', dbPath);
      
      // 创建数据库连接
      this.db = new sqlite3.Database(dbPath, (err) => {
        if (err) {
          console.error('数据库连接失败:', err);
          this.isInitialized = false;
        } else {
          console.log('数据库连接成功:', dbPath);
          this.isInitialized = true;
          
          // 启用外键约束
          this.db.run('PRAGMA foreign_keys = ON');
          
          // 初始化迁移工具
          this.migration = new DatabaseMigration(this.db);
          
          // 创建表结构
          this.createTables();
        }
      });
    } catch (error) {
      console.error('数据库初始化失败:', error);
      this.isInitialized = false;
    }
  }

  // 检查表是否存在
  async tableExists(tableName) {
    return new Promise((resolve, reject) => {
      this.db.get(
        `SELECT name FROM sqlite_master WHERE type='table' AND name=?`,
        [tableName],
        (err, row) => {
          if (err) {
            reject(err);
          } else {
            resolve(!!row);
          }
        }
      );
    });
  }

  createTables() {
    try {
      // 读取 SQL 文件
      const sqlFilePath = path.join(__dirname, 'schema.sql');
      
      // 检查 SQL 文件是否存在
      if (!fs.existsSync(sqlFilePath)) {
        console.error('SQL 文件不存在:', sqlFilePath);
        return;
      }
      
      // 读取 SQL 文件内容
      const sqlContent = fs.readFileSync(sqlFilePath, 'utf8');
      
      // 检查关键表是否已存在
      this.tableExists('users').then((usersExists) => {
        if (usersExists) {
          // 表已存在，跳过创建，直接执行迁移
          console.log('数据库表已存在，跳过创建');
          this.runMigrations();
        } else {
          // 表不存在，执行创建
          // 注意：schema.sql 中包含 DROP TABLE 语句，但因为我们检查了表不存在，所以 DROP 不会影响已有数据
          this.db.exec(sqlContent, (err) => {
            if (err) {
              console.error('执行 SQL 文件失败:', err);
            } else {
              console.log('数据库表结构创建成功');
              
              // 执行数据库迁移
              this.runMigrations();
            }
          });
        }
      }).catch((error) => {
        console.error('检查表是否存在失败:', error);
        // 如果检查失败，尝试直接创建（可能是首次运行）
        this.db.exec(sqlContent, (err) => {
          if (err) {
            console.error('执行 SQL 文件失败:', err);
          } else {
            console.log('数据库表结构创建成功');
            this.runMigrations();
          }
        });
      });
      
    } catch (error) {
      console.error('读取 SQL 文件失败:', error);
    }
  }

  // 执行数据库迁移
  async runMigrations() {
    try {
      if (this.migration) {
        await this.migration.migrate();
      }
    } catch (error) {
      console.error('数据库迁移失败:', error);
    }
  }

  // 通用CRUD操作
  insert(tableName, data) {
    return new Promise((resolve, reject) => {
      if (!this.isInitialized) {
        reject(new Error('数据库未初始化'));
        return;
      }

      const columns = Object.keys(data).join(', ');
      const placeholders = Object.keys(data).map(() => '?').join(', ');
      const values = Object.values(data);
      
      const sql = `INSERT INTO ${tableName} (${columns}) VALUES (${placeholders})`;
      this.db.run(sql, values, function(err) {
        if (err) {
          reject(err);
        } else {
          resolve(this.lastID);
        }
      });
    });
  }

  async update(tableName, id, data) {
    return new Promise(async (resolve, reject) => {
      if (!this.isInitialized) {
        reject(new Error('数据库未初始化'));
        return;
      }

      try {
        // 检查表是否有 updated_at 列
        const tableInfo = await new Promise((resolveInfo, rejectInfo) => {
          this.db.all(`PRAGMA table_info(${tableName})`, [], (err, rows) => {
            if (err) {
              rejectInfo(err);
            } else {
              resolveInfo(rows);
            }
          });
        });
        
        const hasUpdatedAt = tableInfo.some(col => col.name === 'updated_at');
        
        const setClause = Object.keys(data).map(key => `${key} = ?`).join(', ');
        const values = Object.values(data);
        
        // 只有当表有 updated_at 列时才更新它
        let sql = `UPDATE ${tableName} SET ${setClause}`;
        if (hasUpdatedAt) {
          sql += `, updated_at = CURRENT_TIMESTAMP`;
        }
        sql += ` WHERE id = ?`;
        
        values.push(id);
        
        this.db.run(sql, values, function(err) {
          if (err) {
            reject(err);
          } else {
            resolve(this.changes);
          }
        });
      } catch (error) {
        reject(error);
      }
    });
  }

  delete(tableName, id) {
    return new Promise((resolve, reject) => {
      if (!this.isInitialized) {
        reject(new Error('数据库未初始化'));
        return;
      }

      const sql = `DELETE FROM ${tableName} WHERE id = ?`;
      this.db.run(sql, [id], function(err) {
        if (err) {
          reject(err);
        } else {
          resolve(this.changes);
        }
      });
    });
  }

  findById(tableName, id) {
    return new Promise((resolve, reject) => {
      if (!this.isInitialized) {
        reject(new Error('数据库未初始化'));
        return;
      }

      const sql = `SELECT * FROM ${tableName} WHERE id = ?`;
      this.db.get(sql, [id], (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row);
        }
      });
    });
  }

  findAll(tableName, conditions = {}, pagination = null) {
    return new Promise((resolve, reject) => {
      if (!this.isInitialized) {
        reject(new Error('数据库未初始化'));
        return;
      }

      let sql = `SELECT * FROM ${tableName}`;
      const values = [];
      
      if (Object.keys(conditions).length > 0) {
        const whereClause = Object.keys(conditions).map(key => `${key} = ?`).join(' AND ');
        sql += ` WHERE ${whereClause}`;
        values.push(...Object.values(conditions));
      }
      
      // 支持分页
      if (pagination && pagination.limit) {
        sql += ` LIMIT ?`;
        values.push(pagination.limit);
        
        if (pagination.offset) {
          sql += ` OFFSET ?`;
          values.push(pagination.offset);
        }
      }
      
      this.db.all(sql, values, (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  // 获取符合条件的记录总数（用于分页）
  count(tableName, conditions = {}) {
    return new Promise((resolve, reject) => {
      if (!this.isInitialized) {
        reject(new Error('数据库未初始化'));
        return;
      }

      let sql = `SELECT COUNT(*) as total FROM ${tableName}`;
      const values = [];
      
      if (Object.keys(conditions).length > 0) {
        const whereClause = Object.keys(conditions).map(key => `${key} = ?`).join(' AND ');
        sql += ` WHERE ${whereClause}`;
        values.push(...Object.values(conditions));
      }
      
      this.db.get(sql, values, (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row ? row.total : 0);
        }
      });
    });
  }

  // 文件相关操作
  insertFile(recordId, recordType, uploadAreaKey, fileName, filePath, fileSize, fileType) {
    return this.insert('files', {
      record_id: recordId,
      record_type: recordType,
      upload_area_key: uploadAreaKey,
      file_name: fileName,
      file_path: filePath,
      file_size: fileSize,
      file_type: fileType
    });
  }

  getFiles(recordId, recordType) {
    return new Promise((resolve, reject) => {
      if (!this.isInitialized) {
        reject(new Error('数据库未初始化'));
        return;
      }

      const sql = `
        SELECT * FROM files 
        WHERE record_id = ? AND record_type = ?
        ORDER BY upload_area_key, created_at
      `;
      this.db.all(sql, [recordId, recordType], (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  deleteFile(fileId) {
    return new Promise((resolve, reject) => {
      if (!this.isInitialized) {
        reject(new Error('数据库未初始化'));
        return;
      }

      const sql = 'DELETE FROM files WHERE id = ?';
      this.db.run(sql, [fileId], function(err) {
        if (err) {
          reject(err);
        } else {
          resolve(this.changes);
        }
      });
    });
  }

  close() {
    return new Promise((resolve) => {
      if (this.db) {
        this.db.close((err) => {
          if (err) {
            console.error('关闭数据库失败:', err);
          } else {
            console.log('数据库已关闭');
          }
          resolve();
        });
      } else {
        resolve();
      }
    });
  }
}

module.exports = DatabaseManager;